// 注意：作者已弃用此类！！！

package utils

import (
	"context"
	"database/sql"
	"fmt"
	"github.com/go-sql-driver/mysql"
	"math"
	"runtime"
	"strings"
	"sync"
	"sync/atomic"
	"time"
)

const (
	DbMaster = "master"
	DbSlave  = "slave"
)

type Model struct {
	PK            string
	Table         string
	CfgName       string
	DSN           string
	logger        *Queue
	masterAsSlave bool
	TimeoutInsert time.Duration
	TimeoutSelect time.Duration
	TimeoutUpdate time.Duration
	TimeoutTrans  time.Duration
}

type ModelItem map[string]interface{}

type DbMS struct {
	m *sql.DB
	s *sql.DB
}

type DbMap struct {
	kv map[string]*DbMS
	l  *sync.RWMutex
	c  *uint64
}

var dbMap *DbMap

func init() {
	var c uint64 = 0
	dbMap = &DbMap{
		kv: map[string]*DbMS{},
		l:  new(sync.RWMutex),
		c:  &c,
	}
}

// GetSqlOpenCnt 取连接打开次数
func GetSqlOpenCnt() uint64 {
	return *(dbMap.c)
}

// NewModel 新模型，参数依次为：表名、主键名、配置中的库名
func NewModel(table, pk, cfgName string) (*Model, error) {
	if table == "" || pk == "" || cfgName == "" {
		return nil, fmt.Errorf("wrong params for NewModel")
	}
	log, _ := NewQueue(20) // 记录最近的查询日志
	dsn := ""
	if _, err := mysql.ParseDSN(cfgName); err == nil {
		dsn = cfgName
		cfgName = StrMd5(cfgName)
	}
	return &Model{
		PK:            pk,
		Table:         table,
		CfgName:       cfgName,
		DSN:           dsn,
		logger:        log,
		masterAsSlave: true, // 默认不使用从库
		TimeoutSelect: 3 * time.Second,
		TimeoutInsert: 3 * time.Second,
		TimeoutUpdate: 3 * time.Second,
		TimeoutTrans:  5 * time.Second,
	}, nil
}

// PingDB 检查连接存活状态，1s 超时
func PingDB(db *sql.DB) error {
	ctx, cancel := context.WithTimeout(context.Background(), 1*time.Second)
	defer cancel()
	return db.PingContext(ctx)
}

// SetMasterAsSlave 是否把主库当从库用
func (m *Model) SetMasterAsSlave(b bool) {
	m.masterAsSlave = b
}

// sqlOpen 打开连接，使用配置中的连接
func sqlOpen(d, k, dsn string) (*sql.DB, error) {
	dbMap.l.RLock()
	dbMs, ok := dbMap.kv[d]
	dbMap.l.RUnlock()
	if ok && dbMs != nil {
		if k == DbMaster && dbMs.m != nil {
			return dbMs.m, nil
		}
		if k == DbSlave && dbMs.s != nil {
			return dbMs.s, nil
		}
	}
	dbMap.l.Lock()
	defer dbMap.l.Unlock()
	dbMs, ok = dbMap.kv[d]
	if !ok || dbMs == nil {
		dbMs = &DbMS{
			m: nil,
			s: nil,
		}
	} else {
		if k == DbMaster && dbMs.m != nil {
			return dbMs.m, nil
		}
		if k == DbSlave && dbMs.s != nil {
			return dbMs.s, nil
		}
	}
	fk := "_" + k
	if dsn == "" {
		if dsn, _ = CfgGetStr(d + "_dsn" + fk); dsn == "" {
			if dsn, _ = CfgGetStr(d + "_dsn"); dsn != "" {
				fk = ""
			}
		}
	}
	var db *sql.DB
	var err error
	db, err = sql.Open("mysql", dsn)
	if err != nil {
		return nil, err
	}
	if err = PingDB(db); err != nil {
		return nil, err
	}
	defer atomic.AddUint64(dbMap.c, 1)
	maxOpen, _ := CfgGetInt(d + "_max_open" + fk)
	if maxOpen <= 0 {
		maxOpen = runtime.NumCPU()*2 + 1
	}
	db.SetMaxOpenConns(maxOpen)
	maxIdle, _ := CfgGetInt(d + "_max_idle" + fk)
	if maxIdle <= 0 {
		maxIdle = maxOpen / 2
		if maxIdle < 2 {
			maxIdle = 2
		}
	}
	db.SetMaxIdleConns(maxIdle)
	db.SetConnMaxIdleTime(10 * time.Minute) // 最大空闲十分钟
	db.SetConnMaxLifetime(0)
	if k == DbMaster {
		dbMs.m = db
	} else if k == DbSlave {
		dbMs.s = db
	}
	if !ok {
		dbMap.kv[d] = dbMs
	}
	return db, nil
}

// GetDB 取连接
func (m *Model) GetDB(k string) (*sql.DB, error) {
	if k != DbMaster && k != DbSlave {
		return nil, fmt.Errorf("must MASTER or SLAVE")
	}
	if m.masterAsSlave {
		k = DbMaster
	}
	if db, err := sqlOpen(m.CfgName, k, m.DSN); err != nil {
		return nil, err
	} else {
		return db, nil
	}
}

// CloseDB 关闭连接
func (m *Model) CloseDB() {
	dbMap.l.Lock()
	defer dbMap.l.Unlock()
	if dbMs, _ := dbMap.kv[m.CfgName]; dbMs != nil {
		if dbMs.m != nil {
			_ = dbMs.m.Close()
			dbMs.m = nil
		}
		if dbMs.s != nil {
			_ = dbMs.s.Close()
			dbMs.s = nil
		}
	}
}

// Truncate 清空表
func (m *Model) Truncate() error {
	db, err := m.GetDB(DbMaster)
	if err != nil {
		return err
	}
	q := "TRUNCATE TABLE `" + m.Table + "`"
	ctx, cancel := context.WithTimeout(context.Background(), m.TimeoutUpdate)
	defer cancel()
	defer func() {
		m.AddQueryLog(q, nil, err)
	}()
	_, err = db.ExecContext(ctx, q)
	return err
}

// Insert 插入一条数据
func (m *Model) Insert(item *ModelItem, ignore bool) (int64, error) {
	if item == nil || *item == nil || len(*item) == 0 {
		return 0, fmt.Errorf("item is empty")
	}
	var cols []string
	var params []interface{}
	var phds []string
	for col, param := range *item {
		cols = append(cols, StrCamelToCase(col))
		params = append(params, param)
		phds = append(phds, "?")
	}
	query := "INSERT "
	if ignore {
		query += "IGNORE "
	}
	query += "INTO `" + m.Table + "` (`" + StrImplode("`, `", cols...) + "`) VALUES (" + StrImplode(", ", phds...) + ")"
	db, err := m.GetDB(DbMaster)
	if err != nil {
		return 0, err
	}
	var res sql.Result
	ctx, cancel := context.WithTimeout(context.Background(), m.TimeoutInsert)
	defer cancel()
	defer func() {
		m.AddQueryLog(query, params, err)
	}()
	res, err = db.ExecContext(ctx, query, params...)
	if err != nil {
		return 0, err
	}
	var newId int64
	newId, err = res.LastInsertId()
	if err != nil {
		return 0, err
	}
	return newId, nil
}

// InsertBatch 批量插入，返回成功条数
func (m *Model) InsertBatch(items []*ModelItem) (int64, error) {
	n := len(items)
	if n == 0 {
		return 0, fmt.Errorf("data is empty")
	}
	var cols []string
	var colsReal []string
	var phds []string
	for col := range *(items[0]) {
		cols = append(cols, StrCamelToCase(col))
		colsReal = append(colsReal, col)
		phds = append(phds, "?")
	}
	nc := len(cols)
	if nc == 0 {
		return 0, fmt.Errorf("wrong data format")
	}
	query := "INSERT IGNORE INTO `" + m.Table + "` (`" + StrImplode("`, `", cols...) + "`) VALUES (" + StrImplode(", ", phds...) + ")"
	db, err := m.GetDB(DbMaster)
	if err != nil {
		return 0, err
	}
	var stmt *sql.Stmt
	ctx, cancel := context.WithTimeout(context.Background(), m.TimeoutInsert)
	defer cancel()
	if stmt, err = db.PrepareContext(ctx, query); err != nil {
		return 0, err
	}
	defer stmt.Close()
	var res sql.Result
	var affected int64
	var ret int64
	var col string
	var params []interface{}
	var logs []interface{}
	defer func() {
		m.logger.Push(logs...)
	}()
	for _, item := range items {
		params = make([]interface{}, nc)
		for n, col = range colsReal {
			params[n] = (*item)[col]
		}
		res, err = stmt.ExecContext(ctx, params...)
		if err == nil {
			if affected, err = res.RowsAffected(); err == nil {
				ret += affected
			} else {
				return ret, err
			}
		} else {
			return ret, err
		}
		logs = append(logs, &QueryLog{
			When:   TimeNowNice(),
			Query:  query,
			Params: params,
			Err:    err,
		})
	}
	return ret, nil
}

// Select 查询，支持占位符
func (m *Model) Select(query string, params ...interface{}) (items []*ModelItem, err error) {
	err = m.SelectWalk(func(item *ModelItem) error {
		items = append(items, item)
		return nil
	}, query, params...)
	return
}

// SelectWalk 逐行处理查询结果
func (m *Model) SelectWalk(fn func(item *ModelItem) error, query string, params ...interface{}) error {
	if strings.ToLower(query[0:7]) != "select " || len(query) < 8 {
		return fmt.Errorf("wrong select sql")
	}
	db, err := m.GetDB(DbSlave)
	if err != nil {
		return err
	}
	var rows *sql.Rows
	ctx, cancel := context.WithTimeout(context.Background(), m.TimeoutSelect)
	defer cancel()
	defer func() {
		m.AddQueryLog(query, params, err)
	}()
	rows, err = db.QueryContext(ctx, query, params...)
	if err != nil {
		return err
	}
	defer rows.Close()
	var cols []string
	cols, err = rows.Columns()
	if err != nil {
		return err
	}
	var i, colsLen = 0, len(cols)
	var item *ModelItem
	var bs sql.RawBytes
	var rawBuffers []sql.RawBytes
	var scanArgs []interface{}
	for rows.Next() {
		rawBuffers = make([]sql.RawBytes, colsLen)
		scanArgs = make([]interface{}, colsLen)
		for i = range rawBuffers {
			scanArgs[i] = &rawBuffers[i]
		}
		err = rows.Scan(scanArgs...)
		if err != nil {
			return err
		}
		item = &ModelItem{}
		for i, bs = range rawBuffers {
			(*item)[StrCaseToCamel(cols[i])] = string(bs)
		}
		if err = fn(item); err != nil {
			return err
		}
	}
	return nil
}

// SelectPage 按页取数据
func (m *Model) SelectPage(page, numPerPage int64, queryWhere, queryOrder, selCols string, fn func(item *ModelItem) error, params ...interface{}) (totalItem, totalPage, curPage int64, err error) {
	if StrTrim(queryWhere) == "" {
		queryWhere = "1 = 1"
	}
	if StrTrim(queryOrder) == "" {
		queryOrder = "`" + m.PK + "` ASC"
	}
	if StrTrim(selCols) == "" {
		selCols = "*"
	}
	qCount := "SELECT COUNT(*) FROM `" + m.Table + "` WHERE " + queryWhere
	totalItem, err = m.Count(qCount, params...)
	if err != nil {
		return
	}
	if totalItem == 0 {
		totalPage = 0
		curPage = 1
		return
	}
	totalPage = int64(math.Ceil(float64(totalItem) / float64(numPerPage)))
	if page < 1 {
		page = 1
	}
	if page > totalPage {
		page = totalPage
	}
	curPage = page
	qSelect := "SELECT " + selCols + " FROM `" + m.Table + "` WHERE " + queryWhere + " ORDER BY " + queryOrder + " LIMIT ?, ?"
	params = append(params, (curPage-1)*numPerPage, numPerPage)
	err = m.SelectWalk(fn, qSelect, params...)
	return
}

// GetById 按主键查询数据
func (m *Model) GetById(id string, cols ...string) (*ModelItem, error) {
	if id = StrTrim(id); id == "" {
		return nil, fmt.Errorf("id is empty")
	}
	queryCols := "*"
	if len(cols) > 0 {
		queryCols = "`" + StrImplode("`, `", cols...) + "`"
	}
	items, err := m.Select("SELECT "+queryCols+" FROM `"+m.Table+"` WHERE `"+m.PK+"` = ? LIMIT 1", id)
	if err != nil {
		return nil, err
	}
	if len(items) > 0 {
		return items[0], nil
	}
	return nil, nil
}

// IdsWalk 逐个处理数据
func (m *Model) IdsWalk(fn func(item *ModelItem) error, ids []interface{}, cols ...string) error {
	idsNum := len(ids)
	if idsNum == 0 {
		return fmt.Errorf("no id")
	}
	queryCols := "*"
	if len(cols) > 0 {
		queryCols = "`" + StrImplode("`, `", cols...) + "`"
	}
	phds := strings.TrimRight(strings.Repeat(" ?,", idsNum), ",")
	return m.SelectWalk(func(item *ModelItem) error {
		return fn(item)
	}, "SELECT "+queryCols+" FROM `"+m.Table+"` WHERE `"+m.PK+"` IN ("+phds+" )", ids...)
}

// GetByIds 按多个ID取数据
func (m *Model) GetByIds(ids []interface{}, cols ...string) (items []*ModelItem, err error) {
	err = m.IdsWalk(func(item *ModelItem) error {
		items = append(items, item)
		return nil
	}, ids, cols...)
	return
}

// Count 统计数据条数
func (m *Model) Count(query string, params ...interface{}) (int64, error) {
	if strings.ToLower(query[0:13]) != "select count(" || len(query) < 14 {
		return 0, fmt.Errorf("wrong select count sql")
	}
	db, err := m.GetDB(DbSlave)
	if err != nil {
		return 0, err
	}
	var cnt int64
	ctx, cancel := context.WithTimeout(context.Background(), m.TimeoutSelect)
	defer cancel()
	defer func() {
		m.AddQueryLog(query, params, err)
	}()
	if err = db.QueryRowContext(ctx, query, params...).Scan(&cnt); err != nil {
		return 0, err
	}
	return cnt, nil
}

// Update 更新数据
func (m *Model) Update(query string, params ...interface{}) (int64, error) {
	if sqlStart7 := strings.ToLower(query[0:7]); (sqlStart7 != "update " && sqlStart7 != "delete ") || len(query) < 8 {
		return 0, fmt.Errorf("wrong update or delete sql")
	}
	db, err := m.GetDB(DbMaster)
	if err != nil {
		return 0, err
	}
	var res sql.Result
	ctx, cancel := context.WithTimeout(context.Background(), m.TimeoutUpdate)
	defer cancel()
	defer func() {
		m.AddQueryLog(query, params, err)
	}()
	res, err = db.ExecContext(ctx, query, params...)
	if err != nil {
		return 0, err
	}
	var affected int64
	affected, err = res.RowsAffected()
	if err != nil {
		return 0, err
	}
	return affected, nil
}

// UpdateById 按ID更新数据
func (m *Model) UpdateById(item *ModelItem, id string) (int64, error) {
	colsNum := len(*item)
	id = StrTrim(id)
	if colsNum == 0 || id == "" {
		return 0, fmt.Errorf("wrong item or id")
	}
	sets := make([]string, colsNum)
	params := make([]interface{}, colsNum+1)
	i := 0
	for col, param := range *item {
		col = StrCamelToCase(col)
		if col == m.PK {
			continue
		}
		sets[i] = "`" + col + "` = ?"
		params[i] = param
		i++
	}
	if i == 0 {
		return 0, fmt.Errorf("wrong item")
	}
	params[i] = id
	return m.Update("UPDATE `"+m.Table+"` SET "+StrImplode(", ", sets...)+" WHERE `"+m.PK+"` = ?", params...)
}

// Delete 删除数据
func (m *Model) Delete(query string, params ...interface{}) (int64, error) {
	if strings.ToLower(query[0:7]) != "delete " || len(query) < 8 {
		return 0, fmt.Errorf("wrong delete sql")
	}
	return m.Update(query, params...)
}

// DelByIds 按主键删除数据
func (m *Model) DelByIds(ids ...interface{}) (int64, error) {
	idsNum := len(ids)
	if idsNum == 0 {
		return 0, fmt.Errorf("no id")
	}
	var i int64
	var err error
	if idsNum == 1 {
		if ids[0] == "" {
			return 0, fmt.Errorf("id is empty")
		}
		i, err = m.Delete("DELETE FROM `"+m.Table+"` WHERE `"+m.PK+"` = ?", ids[0])
	} else {
		phds := strings.TrimRight(strings.Repeat(" ?,", idsNum), ",")
		i, err = m.Delete("DELETE FROM `"+m.Table+"` WHERE `"+m.PK+"` IN ("+phds+" )", ids...)
	}
	if err != nil {
		return 0, err
	}
	return i, nil
}

// Transaction 使用事务
func (m *Model) Transaction(handle func(tx *sql.Tx, logger *Queue) error) error {
	var tx *sql.Tx
	db, err := m.GetDB(DbMaster)
	if err != nil {
		return err
	}
	ctx, cancel := context.WithTimeout(context.Background(), m.TimeoutTrans)
	defer cancel()
	tx, err = db.BeginTx(ctx, nil)
	if err != nil {
		return err
	}
	defer func() { // 这里会不会有坑？
		tried := 0
		for err != nil && tried < 3 {
			err = tx.Rollback()
			tried++
		}
	}()
	if err = handle(tx, m.logger); err != nil {
		return err
	}
	err = tx.Commit()
	return err
}

// AddQueryLog 记录查询日志
func (m *Model) AddQueryLog(query string, params []interface{}, err error) {
	m.logger.Push(&QueryLog{
		When:   TimeNowNice(),
		Query:  query,
		Params: params,
		Err:    err,
	})
}

// GetQueryLogs 获取最近的查询日志
func (m *Model) GetQueryLogs() (ret []*QueryLog) {
	for {
		if log, _ := m.logger.Pop(); log == nil {
			break
		} else if ql, ok := log.(*QueryLog); ok {
			ret = append(ret, ql)
		}
	}
	return ret
}

// CloseQueryLog 关闭或打开日志记录
func (m *Model) CloseQueryLog(b bool) {
	m.logger.Pause(b)
}
